﻿Imports System.IO
Imports Microsoft.Office.Interop
Imports System.Data
Imports ExcelLibrary

Partial Class AdminNewReport
    Inherits Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

        Dim type = Request.QueryString("type")

        Response.Clear()
        Response.ContentType = "application/vnd.ms-excel"


        If type = "taxists" Then
            Dim isOnline = ""
            Dim cityId = Guid.Empty
            Dim online = False


            If Request.QueryString.Keys.Count > 0 Then
                Try
                    isOnline = Request.QueryString("isonline")
                    cityId = Guid.Parse(Request.QueryString("cityid").ToString())
                Catch ex As Exception

                End Try
            End If

            If isOnline = "on" Then
                online = True
            End If

            Response.AddHeader("content-disposition", "attachment;filename=taxists.xls")

            Dim ds = New DataSet("taxists")
            Using context As New TaxiEntities()
                Dim tb As DataTable = New DataTable("Таксисты")
                tb.Columns.Add("ФИО")
                tb.Columns.Add("Телефон")
                tb.Columns.Add("Пароль")
                tb.Columns.Add("Автомобиль")
                tb.Columns.Add("Дата регистрации")
                tb.Columns.Add("Город")

                ds.Tables.Add(tb)

                Dim index = 1

                For Each t In context.taxist.Include("transfers").Include("cities").Where(CType(Function(a) a.taxi_isonline = online And (cityId = Guid.Empty Or a.city_id = cityId), Func(Of taxist, Boolean))).OrderByDescending(Function(a) a.taxi_created).ToList()
                    tb.Rows.Add(t.taxi_lastname & " " & t.taxt_firstname, t.taxi_phone, t.taxi_password, t.taxi_marka + " " + t.taxi_model + " " + t.taxi_carnumber + " " + t.taxi_color, t.taxi_created.ToString("dd MMMM yyyy г. HH:mm"), t.cities.city_name)

                    index += 1
                Next

                For i = index To 100
                    tb.Rows.Add("", "", "", "", "", "")
                Next


            End Using
            Dim m = New MemoryStream()
            DataSetHelper.CreateWorkbook(m, ds)
            m.WriteTo(Response.OutputStream)
            Response.End()
            Return
        End If

        If type = "orders" Then
            Response.AddHeader("content-disposition", "attachment;filename=orders.xls")

            Dim cityId = Guid.Empty
            Dim periodFrom = ""
            Dim periodTo = ""

            Dim periodFromDate = Date.MinValue
            Dim periodToDate = Date.MaxValue

            If Request.QueryString.Keys.Count > 0 Then
                Try
                    cityId = Guid.Parse(Request.QueryString("cityid").ToString())
                    periodFrom = Request.QueryString("period_from")
                    periodTo = Request.QueryString("period_to")
                Catch ex As Exception
                End Try
            End If

            If String.IsNullOrEmpty(periodFrom) <> True Then
                periodFromDate = New Date(CType(periodFrom.Split(".")(2), Integer), CType(periodFrom.Split(".")(1), Integer), CType(periodFrom.Split(".")(0), Integer))
            End If

            If String.IsNullOrEmpty(periodTo) <> True Then
                periodToDate = New Date(CType(periodTo.Split(".")(2), Integer), CType(periodTo.Split(".")(1), Integer), CType(periodTo.Split(".")(0), Integer))
            End If


            Dim ds = New DataSet("orders")
            Using context As New TaxiEntities()
                Dim tb As DataTable = New DataTable("Таксисты")
                tb.Columns.Add("ФИО")
                tb.Columns.Add("Телефон")
                tb.Columns.Add("Телефон клиента")
                tb.Columns.Add("Город")
                tb.Columns.Add("Откуда")
                tb.Columns.Add("Куда")
                tb.Columns.Add("Примечание")
                tb.Columns.Add("Дата создания")
                tb.Columns.Add("Время ожидания")
                tb.Columns.Add("Цена")

                ds.Tables.Add(tb)

                Dim index = 1

                For Each t In context.torders.Include("taxist").Include("orders").Include("taxist.cities").Where(CType(Function(a) a.torder_status > 6 And periodFromDate <= a.torder_created And a.torder_created <= periodToDate And (cityId = Guid.Empty Or a.taxist.city_id = cityId), Func(Of torders, Boolean))).OrderByDescending(Function(a) a.torder_created).ToList()
                    tb.Rows.Add(t.taxist.taxi_lastname & " " & t.taxist.taxt_firstname, t.taxist.taxi_phone, t.orders.order_user_phone, _
                                t.taxist.cities.city_name, t.orders.order_from_address, t.orders.order_to_address, _
                                IIf(IsNothing(t.orders.order_comment) Or t.orders.order_comment = "Примечаний нет!", "", t.orders.order_comment), CType(t.orders.order_create, DateTime).AddHours(3).ToString("dd MMMM yyyy г. HH:mm").Replace(",", ""), _
                                t.orders.order_time, IIf(t.torder_price = 0, "", t.torder_price))
                    index += 1
                Next


                For i = index To 100
                    tb.Rows.Add("", "", "", "", "", "", "", "", "", "")
                Next

            End Using
            Dim m = New MemoryStream()
            DataSetHelper.CreateWorkbook(m, ds)
            m.WriteTo(Response.OutputStream)
            Response.End()
        End If

        If type = "phones" Then
            Response.AddHeader("content-disposition", "attachment;filename=phones.xls")

            Dim ds = New DataSet("phones")
            Using context As New TaxiEntities()
                Dim tb As DataTable = New DataTable("Таксисты")
                tb.Columns.Add("ФИО")
                tb.Columns.Add("Телефон")
                tb.Columns.Add("Дата регистрации")
                tb.Columns.Add("Город")

                ds.Tables.Add(tb)


                Dim index = 1

                For Each t In context.taxist.Include("cities").OrderByDescending(Function(a) a.taxi_created).OrderByDescending(Function(a) a.taxi_created).ToList()
                    tb.Rows.Add(t.taxi_lastname & " " & t.taxt_firstname, t.taxi_phone, CType(t.taxi_created, DateTime).ToString("dd MMMM yyyy г. HH:mm").Replace(",", ""), t.cities.city_name)
                    index += 1
                Next

                For i = index To 100
                    tb.Rows.Add("", "", "", "")
                Next

                tb = New DataTable("Клиенты")
                tb.Columns.Add("Телефон")
                tb.Columns.Add("Город")
                tb.Columns.Add("Дата регистрации")

                ds.Tables.Add(tb)
                Dim index2 = 1



                Dim ords = context.orders.Include("torders").Include("torders.taxist").Include("torders.taxist.cities").Where(Function(a) String.IsNullOrEmpty(a.order_user_phone) <> True).OrderByDescending(Function(a) a.order_create).ToList()
                Dim distinctords = New List(Of orders)

                For Each o In ords
                    Dim old = distinctords.FirstOrDefault(Function(a) a.order_user_phone = o.order_user_phone)
                    If IsNothing(old) Then
                        distinctords.Add(o)
                    End If
                Next

                For Each o In distinctords

                    Dim cityname As String = ""
                    If IsNothing(o.torders) <> True And o.torders.Count > 0 Then
                        Dim torder = o.torders.FirstOrDefault()
                        If IsNothing(torder) <> True Then
                            cityname = torder.taxist.cities.city_name
                        End If
                    End If

                    tb.Rows.Add(o.order_user_phone,
                                cityname,
                                CType(o.order_create, DateTime).AddHours(3).ToString("dd MMMM yyyy г. HH:mm"))
                    index2 += 1
                Next




                For i = index2 To 100
                    tb.Rows.Add("", "", "")
                Next
            End Using
            Dim m = New MemoryStream()
            DataSetHelper.CreateWorkbook(m, ds)
            m.WriteTo(Response.OutputStream)
            Response.End()
        End If
    End Sub


End Class
